{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "day = datetime.datetime.strftime(datetime.datetime.now(), \"%Y_%m_%d\")\n",
    "day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "! rm -rf \"eqList/eqList\"$day\".xls\"\n",
    "! cp \"/Users/ivan/Downloads/eqList\"$day\".xls\" \"eqList/eqList\"$day\".xls\"\n",
    "! ls -l \"eqList/\"\n",
    "\n",
    "aeta = f\"eqList/eqList{day}.xls\"\n",
    "aeta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 1\n",
    "T = 10\n",
    "# 经度\n",
    "lonL, lonH = 98, 107\n",
    "# 纬度\n",
    "latL, latH = 22, 34 \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import xml\n",
    "DOMTree = xml.dom.minidom.parse(aeta).documentElement\n",
    "Row = DOMTree.getElementsByTagName(\"Row\")\n",
    "\n",
    "cols, data = [], []\n",
    "for nRow, iRow in enumerate(Row):\n",
    "    if nRow == 0:\n",
    "        for jRow in iRow.getElementsByTagName(\"Cell\"):\n",
    "            cols.append(jRow.childNodes[0].childNodes[0].data)\n",
    "    else:\n",
    "        idata = []\n",
    "        for jRow in iRow.getElementsByTagName(\"Cell\"):\n",
    "            idata.append(jRow.childNodes[0].childNodes[0].data)\n",
    "        data.append([float(dj) if nj in [1,2,3,4] else str(dj) for nj ,dj in enumerate(idata)])\n",
    "\n",
    "data = pd.DataFrame(data, columns=cols)\n",
    "print(data.dtypes)\n",
    "data.head(T)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def regeo(location):\n",
    "    try:\n",
    "        import json\n",
    "        import requests\n",
    "\n",
    "        host = 'https://regeo.market.alicloudapi.com'\n",
    "        path = '/v3/geocode/regeo'\n",
    "        appcode = '830fa522a512421eaa202bb80afe8921'\n",
    "        querys = f'location={location}' # 经度在前，纬度在后\n",
    "        with requests.get(\n",
    "            f\"{host}{path}?{querys}\", \n",
    "            headers={'Authorization': f'APPCODE {appcode}'}\n",
    "        ) as r:\n",
    "            j = json.loads(r.content.decode())[\"regeocode\"][\"formatted_address\"]\n",
    "            return j\n",
    "    except:\n",
    "        return \"NaN\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "\n",
    "Kdata = []\n",
    "for STATION in tqdm(data.head(T)[\"参考位置\"]):\n",
    "    # idata\n",
    "    idata = data[data[\"参考位置\"] == STATION][[\"参考位置\", \"发震时刻\"]]\n",
    "    idata.rename({\n",
    "        \"参考位置\": \"初次参考位置\", \n",
    "        \"发震时刻\": \"初次发震时刻\",\n",
    "    }, axis=1, inplace=True)\n",
    "    idata[\"K\"] = 1\n",
    "    # print(idata.shape)\n",
    "\n",
    "    # jdata\n",
    "    jdata = data[\n",
    "        (data[\"参考位置\"] != STATION) & \n",
    "        (data[\"经度(°)\"] >= lonL) & (data[\"经度(°)\"] <= lonH) &\n",
    "        (data[\"纬度(°)\"] >= latL) & (data[\"纬度(°)\"] <= latH) &\n",
    "        # (data[\"震级(M)\"] >= 3.5) &\n",
    "        True\n",
    "    ][[\"参考位置\", \"震级(M)\", \"纬度(°)\", \"经度(°)\", \"发震时刻\"]]\n",
    "    jdata[\"K\"] = 1\n",
    "    # print(jdata.shape)\n",
    "\n",
    "    # k0data\n",
    "    k0data = pd.merge(\n",
    "        idata,\n",
    "        jdata,\n",
    "        on=\"K\"\n",
    "    )\n",
    "    # print(k0data.shape)\n",
    "\n",
    "    # k1data\n",
    "    # 筛选近N天内\n",
    "    k0data[\"gap-发震时刻\"] = (\n",
    "        pd.to_datetime(k0data[\"发震时刻\"]) - \n",
    "        pd.to_datetime(k0data[\"初次发震时刻\"])\n",
    "    ).dt.days\n",
    "\n",
    "    k1data = (k0data[\"gap-发震时刻\"] >= 1) & (k0data[\"gap-发震时刻\"] <= N)\n",
    "    # print(pd.value_counts(k1data))\n",
    "\n",
    "    k1data = k0data[k1data]\n",
    "    # print(k1data.shape)\n",
    "\n",
    "    # k2data\n",
    "    k2data = k1data.groupby(\n",
    "        [\"初次参考位置\", \"初次发震时刻\", \"K\"], \n",
    "        as_index=False\n",
    "    ).agg({\n",
    "        \"震级(M)\": \"mean\", # 特殊/余震\n",
    "        \"纬度(°)\": \"mean\",\n",
    "        \"经度(°)\": \"mean\",\n",
    "    }).groupby(\n",
    "        [\"K\"], \n",
    "        as_index=False\n",
    "    ).agg(\n",
    "        {\n",
    "        \"K\": \"sum\",\n",
    "        \"震级(M)\": \"mean\", \n",
    "        \"纬度(°)\": \"mean\",\n",
    "        \"经度(°)\": \"mean\",\n",
    "    })\n",
    "\n",
    "    k2data[\"参考位置\"] = STATION\n",
    "    k2data[\"K%\"] = k2data[\"K\"]/idata.shape[0]\n",
    "    k2data[\"震级C\"] = k2data[\"震级(M)\"].apply(lambda x: \"\" if x >= 3.5 else \"❌\")\n",
    "    k2data[\"经度C\"] = k2data[\"经度(°)\"].apply(lambda x: \"\" if x >= lonL and x <= lonH else \"❌\")\n",
    "    k2data[\"纬度C\"] = k2data[\"纬度(°)\"].apply(lambda x: \"\" if x >= latL and x <= latH else \"❌\")\n",
    "    k2data[\"经纬度解析\"] = [regeo(f\"{_1},{_2}\") for _1, _2 in zip(k2data[\"经度(°)\"], k2data[\"纬度(°)\"])]\n",
    "    k2data[\"经度(°)\"] = k2data[\"经度(°)\"].apply(lambda x: f\"{x:.6f}\")\n",
    "    k2data[\"纬度(°)\"] = k2data[\"纬度(°)\"].apply(lambda x: f\"{x:.6f}\")\n",
    "\n",
    "    k2data = k2data[\n",
    "        ['参考位置', '震级(M)', '震级C', '纬度(°)', '纬度C', '经度(°)', '经度C', 'K', 'K%','经纬度解析']\n",
    "    ]\n",
    "    # print(k2data)\n",
    "    Kdata.append(k2data)\n",
    "\n",
    "    # \n",
    "    del idata, jdata, k0data, k1data, k2data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "endata = pd.merge(\n",
    "    data.head(T).rename(\n",
    "        {icol: f\"#{icol}#\" for icol in data.columns if icol != \"参考位置\"}, \n",
    "        axis=1\n",
    "    ), \n",
    "    pd.concat(Kdata),\n",
    "    on=\"参考位置\"\n",
    ").sort_values(\"#发震时刻#\", ascending=False)\n",
    "\n",
    "endata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# \n",
    "day1 = datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(days=1), \"%Y-%m-%d\")\n",
    "dayn = datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(days=N), \"%Y-%m-%d\")\n",
    "day7 = datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(days=7), \"%Y-%m-%d\")\n",
    "print(f\">>> {day1} To {day7} / {dayn}\")\n",
    "\n",
    "message = f\"\"\"\n",
    "# 有震预测 earthquake prediction\n",
    "check_my_prediction(myToken, '{day1}', '{dayn}', 1, latitude=$_1, longitude=$_2, magnitude=$_3)\n",
    "\"\"\"\n",
    "endata = endata[endata[\"震级C\"] != \"❌\"]\n",
    "for _1, _2, _3 in zip(endata[\"纬度(°)\"], endata[\"经度(°)\"], endata[\"震级(M)\"]):\n",
    "    print(message\n",
    "          .replace(\"$_1\",f\"{float(_1):.6f}\")\n",
    "          .replace(\"$_2\",f\"{float(_2):.6f}\")\n",
    "          .replace(\"$_3\",f\"{float(_3):.1f}\")\n",
    "         )\n",
    "\n",
    "message = f\"\"\"\n",
    "# 无震预测 No earthquake prediction\n",
    "check_my_prediction(myToken, '{day1}', '{day7}', 0)\n",
    "\"\"\"\n",
    "print(message)\n",
    "print()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "! cp Test002.ipynb \"back/Test002-\"$day\".ipynb\"\n",
    "! ls -l back/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
